<?php

/**
 * @Created by DanielTang<Danieltang521@gmail.com>
 * @Date: 2017/2/21
 * @Time: 14:53
 * @Class: excel.php
 * @property excel_model $excel_model
 * @Copyright: JinDing, Inc. Shanghai China. All rights reserved.
 * @Description: 操作excel数据和数据库交换
 */
class excel
{

    public $phpExcel    = null;
    public $defaultRows = array(
        'A',
        'B',
        'C',
        'D',
        'E',
        'F',
        'G',
        'H',
        'I',
        'J',
        'K',
        'L',
        'M',
        'N',
        'O',
        'P',
        'Q',
        'R',
        'S',
        'T',
        'U',
        'V',
        'W',
        'X',
        'Y',
        'Z'
    );

    public function __construct()
    {
        require_once 'Excel/PHPExcel.php';
        $this->phpExcel = new PHPExcel();
    }

    /**
     * @method excel2Array
     * @param $excel
     * @Description:传入excel文件转换成数组输出
     */
    public function excel2Array($excel)
    {

        /**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($excel)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
        } elseif (!$PHPReader->canRead($excel)) {
            die('no Excel');
        }


        $this->phpExcel = $PHPReader->load($excel); // 文档名称
        $objWorksheet = $this->phpExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
        $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
        $arr = array(
            1  => 'A',
            2  => 'B',
            3  => 'C',
            4  => 'D',
            5  => 'E',
            6  => 'F',
            7  => 'G',
            8  => 'H',
            9  => 'I',
            10 => 'J',
            11 => 'K',
            12 => 'L',
            13 => 'M',
            14 => 'N',
            15 => 'O',
            16 => 'P',
            17 => 'Q',
            18 => 'R',
            19 => 'S',
            20 => 'T',
            21 => 'U',
            22 => 'V',
            23 => 'W',
            24 => 'X',
            25 => 'Y',
            26 => 'Z'
        );

        $name = $this->phpExcel->getSheetNames();

        $data['tableName'] = $name[0];


        //echo $highestRow.$highestColumn;

        $rowName = array();
        $value = array();
        for ($currentRow = 1; $currentRow <= $highestRow; $currentRow++) {
            /**从第A列开始输出*ord()将字符转为十进制数*/
            $tmp = array();
            for ($currentColumn = 'A'; $currentColumn <= $highestColumn; $currentColumn++) {
                $val = $objWorksheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
                //$val =  iconv('utf-8', 'gb2312', $val);
                if ($currentRow == 1) {
                    $rowName[] = $val;
                } else {
                    $tmp[] = $val;
                }
            }

            if ($currentRow != 1) {
                $value[] = $tmp;
            }
        }

        $data['rowName'] = $rowName;
        $data['value'] = $value;

        return $data;
    }


    /**
     * @method array2Excel
     * @param $data
     * @Description:
     */
    public function array2Excel($data)
    {

        if (!is_array($data)) {
            exit('不能转换成excel');
        }

        //设置生成的表名
        if (isset($data['tableName'])) {
            $tableName = $data['tableName'];
        } else {
            $tableName = "Daniel" . date("H-m-d");
        }
        $this->phpExcel->getProperties()
            ->setCreator($tableName)
            ->setLastModifiedBy($tableName)
            ->setTitle($tableName)
            ->setSubject($tableName)
            ->setDescription($tableName)
            ->setKeywords($tableName)
            ->setCategory($tableName);

        //设置表格属性
        if (isset($data['rowName'])) {
            $rows = $data['rowName'];
        } else {
            $rows[count($data['value'])] = '';
        }

        foreach ($rows as $k => $row) {
            $this->phpExcel->setActiveSheetIndex(0)->setCellValue($this->defaultRows[$k] . "1", $row);
            //$this->phpExcel->getActiveSheet()->getColumnDimension($this->defaultRows[$k])->setAutoSize(50);
        }

        //设置worksheet
        $this->phpExcel->getActiveSheet()->setTitle($tableName);
        //设置默认第一个sheet
        $this->phpExcel->setActiveSheetIndex(0);
        $this->phpExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(28);
        $this->phpExcel->getActiveSheet()->freezePane('A2');
        $i = 2;
        foreach ($data['value'] as $line) {
            foreach ($line as $ki => $v) {
                $this->phpExcel->getActiveSheet()->setCellValue($this->defaultRows[$ki] . $i, $v);
            }
            $i++;
        }

        $this->phpExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        //$this->phpExcel->getActiveSheet()->getStyle( 'A1:Z1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        //$this->phpExcel->getActiveSheet()->getStyle( 'A1:Z1')->getFill()->getStartColor()->setARGB('FF808080');

        $filename = $data['tableName'];
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
        $objWriter = new PHPExcel_Writer_Excel5($this->phpExcel);
        $objWriter->save('php://output');
    }


    //保存到本地
    public function saveLocal($data){

        if (!is_array($data)) {
            exit('不能转换成excel');
        }

        //设置生成的表名
        if (isset($data['tableName'])) {
            $tableName = $data['tableName'];
        } else {
            $tableName = "Daniel" . date("H-m-d");
        }
        $this->phpExcel->getProperties()
            ->setCreator($tableName)
            ->setLastModifiedBy($tableName)
            ->setTitle($tableName)
            ->setSubject($tableName)
            ->setDescription($tableName)
            ->setKeywords($tableName)
            ->setCategory($tableName);

        //设置表格属性
        if (isset($data['rowName'])) {
            $rows = $data['rowName'];
        } else {
            $rows[count($data['value'])] = '';
        }

        foreach ($rows as $k => $row) {
            $this->phpExcel->setActiveSheetIndex(0)->setCellValue($this->defaultRows[$k] . "1", $row);
            //$this->phpExcel->getActiveSheet()->getColumnDimension($this->defaultRows[$k])->setAutoSize(50);
        }

        //设置worksheet
        $this->phpExcel->getActiveSheet()->setTitle($tableName);
        //设置默认第一个sheet
        $this->phpExcel->setActiveSheetIndex(0);
        $this->phpExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(28);
        $this->phpExcel->getActiveSheet()->freezePane('A2');
        $i = 2;
        foreach ($data['value'] as $line) {
            foreach ($line as $ki => $v) {
                $this->phpExcel->getActiveSheet()->setCellValue($this->defaultRows[$ki] . $i, $v);
            }
            $i++;
        }

        $this->phpExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        //$this->phpExcel->getActiveSheet()->getStyle( 'A1:Z1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        //$this->phpExcel->getActiveSheet()->getStyle( 'A1:Z1')->getFill()->getStartColor()->setARGB('FF808080');

        $filename = $data['tableName'];
        //ob_end_clean();//清除缓冲区,避免乱码
        $objWriter = new PHPExcel_Writer_Excel5($this->phpExcel);
        $objWriter->save("files/".$filename.".xls");
    }
} 